<?php
class Bagi_subsidi_syahriyah extends CI_Model 
{

	function __construct()
	{
		parent::__construct();
	}

	function read()
	{
		$this->db->select('distinct(a_bulan.id_m_th_ajaran),th_ajaran');
		$this->db->from('a_bulan');
		$this->db->join('m_th_ajaran', 'm_th_ajaran.id_m_th_ajaran = a_bulan.id_m_th_ajaran');
		$this ->db->order_by('a_bulan.id_m_th_ajaran','desc');

		$query = $this->db->get();		
		return $query->result();
	}

	function jenjang($id_m_th_ajaran)
	{
		$this->db->select('distinct(a_sy.id_m_j_pendidikan),j_pendidikan');
		$this->db->from('a_bulan');
		$this->db->join('a_sy', 'a_sy.id_m_th_ajaran = a_bulan.id_m_th_ajaran');
		$this->db->join('m_j_pendidikan', 'm_j_pendidikan.id_m_j_pendidikan = a_sy.id_m_j_pendidikan');
		$this->db->where('a_bulan.id_m_th_ajaran',$id_m_th_ajaran);
		$this ->db->order_by('a_sy.id_m_j_pendidikan');

		$query = $this->db->get();		
		return $query->result();
	}

	function bulan($id_m_j_pendidikan,$id_m_th_ajaran)
	{
		$this->db->select('
			id_a_bulan, bulan, (
				select count(b_sub_sy.id_m_tholabah)
				from b_sub_sy
				join m_tholabah on m_tholabah.id_m_tholabah = b_sub_sy.id_m_tholabah
				where id_a_bulan = a_bulan.id_a_bulan
				and id_m_j_pendidikan = '.$id_m_j_pendidikan.'
			) as jml
		');
		$this->db->from('a_bulan');
		$this->db->join('m_bulan', 'm_bulan.id_m_bulan = a_bulan.id_m_bulan');
		$this->db->join('a_sy', 'a_sy.id_m_th_ajaran = a_bulan.id_m_th_ajaran');
		$this->db->where('a_sy.id_m_j_pendidikan',$id_m_j_pendidikan);
		$this->db->where('a_bulan.id_m_th_ajaran',$id_m_th_ajaran);
		$this ->db->order_by('id_a_bulan');

		$query = $this->db->get();		
		return $query->result();
	}


	function tholabah($id_a_bulan,$id_m_j_pendidikan)
	{
		$this->db->select('id_b_sub_sy,b_sub_sy.id_m_tholabah,nama_lengkap,jk,kelas,nama_subsidi,besar_subsidi,(besar_sy - besar_subsidi) AS sisa_tagihan');
		$this->db->from('b_sub_sy');
		$this->db->join('m_tholabah','m_tholabah.id_m_tholabah = b_sub_sy.id_m_tholabah');	
		$this->db->join('b_kelas','b_kelas.id_m_tholabah = b_sub_sy.id_m_tholabah');	
		$this->db->join('a_kelas','a_kelas.id_a_kelas = b_kelas.id_a_kelas');	
		$this->db->join('m_kelas','m_kelas.id_m_kelas = a_kelas.id_m_kelas');	
		$this->db->join('a_subsidi','a_subsidi.id_a_subsidi = b_sub_sy.id_a_subsidi');	
		$this->db->join('a_bulan','a_bulan.id_a_bulan = b_sub_sy.id_a_bulan');	
		$this->db->join('m_th_ajaran','m_th_ajaran.id_m_th_ajaran = a_bulan.id_m_th_ajaran');	
		$this->db->join('a_sy','a_sy.id_m_th_ajaran = a_bulan.id_m_th_ajaran');	
		$this->db->where('b_sub_sy.id_a_bulan',$id_a_bulan);
		$this->db->where('m_tholabah.id_m_j_pendidikan',$id_m_j_pendidikan);
		$this->db->where('a_kelas.id_m_th_ajaran = a_bulan.id_m_th_ajaran');
		$this->db->where('a_sy.id_m_j_pendidikan = m_tholabah.id_m_j_pendidikan');

		$query = $this->db->get();		
		return $query->result();
	}

	function detail_tag($id_a_bulan,$id_m_j_pendidikan,$id_m_th_ajaran)
	{
		$this->db->select('a_bulan.id_m_th_ajaran,id_a_bulan,a_sy.id_m_j_pendidikan, j_pendidikan, bulan, th_ajaran, besar_sy');
		$this->db->from('a_bulan');
		$this->db->join('m_bulan','m_bulan.id_m_bulan = a_bulan.id_m_bulan');	
		$this->db->join('m_th_ajaran','m_th_ajaran.id_m_th_ajaran = a_bulan.id_m_th_ajaran');	
		$this->db->join('a_sy','a_sy.id_m_th_ajaran = a_bulan.id_m_th_ajaran');	
		$this->db->join('m_j_pendidikan','m_j_pendidikan.id_m_j_pendidikan = a_sy.id_m_j_pendidikan');	
		$this->db->where('id_a_bulan',$id_a_bulan);
		$this->db->where('a_sy.id_m_j_pendidikan',$id_m_j_pendidikan);
		$this->db->where('a_bulan.id_m_th_ajaran',$id_m_th_ajaran);

		$query = $this->db->get();		
		return $query->result();
	}

	function subsidi()
	{
		$this->db->select('
			id_a_subsidi, nama_subsidi, total_subsidi,
			(select COALESCE(sum(besar_subsidi),0) from b_sub_sy where id_a_subsidi = a_subsidi.id_a_subsidi) as dipakai_sub_sy,
			(select COALESCE(sum(besar_subsidi),0) from b_sub_non_sy where id_a_subsidi = a_subsidi.id_a_subsidi) as dipakai_sub_non_sy,
			(total_subsidi-(select COALESCE(sum(besar_subsidi),0) from b_sub_sy where id_a_subsidi = a_subsidi.id_a_subsidi)-(select COALESCE(sum(besar_subsidi),0) from b_sub_non_sy where id_a_subsidi = a_subsidi.id_a_subsidi)) as sisa
			', false);
		$this->db->from('a_subsidi');
		$this->db->having('sisa >=1');

		$query = $this->db->get();		
		return $query->result();
	}

	function sisa_subsidi($id_a_subsidi)
	{
		$this->db->select('
			id_a_subsidi, nama_subsidi, total_subsidi,
			(select COALESCE(sum(besar_subsidi),0) from b_sub_sy where id_a_subsidi = a_subsidi.id_a_subsidi) as dipakai_sub_sy,
			(select COALESCE(sum(besar_subsidi),0) from b_sub_non_sy where id_a_subsidi = a_subsidi.id_a_subsidi) as dipakai_sub_non_sy,
			(total_subsidi-
				(select COALESCE(sum(besar_subsidi),0) from b_sub_sy where id_a_subsidi = a_subsidi.id_a_subsidi)-
				(select COALESCE(sum(besar_subsidi),0) from b_sub_non_sy where id_a_subsidi = a_subsidi.id_a_subsidi)
			) as sisa
		',false);
		$this->db->from('a_subsidi');
		$this->db->having('sisa >= 1');
		$this->db->having('id_a_subsidi',$id_a_subsidi);

		$query = $this->db->get();		
		return $query->result();
	}

	function list_tholabah_modal($id_m_j_pendidikan,$id_m_kelas,$id_a_bulan)
	{
		$this->db->select('id_b_kelas,b_kelas.id_m_tholabah, nama_lengkap, alamat, jk');
		$this->db->from('b_kelas');
		$this->db->join('m_tholabah','m_tholabah.id_m_tholabah = b_kelas.id_m_tholabah');
		$this->db->join('a_kelas','a_kelas.id_a_kelas = b_kelas.id_a_kelas');
		$this->db->where('id_m_th_ajaran', id_ta_aktif());
		$this->db->where('m_tholabah.id_m_j_pendidikan', $id_m_j_pendidikan);
		$this->db->where('a_kelas.id_m_kelas', $id_m_kelas);
		$this->db->where('b_kelas.id_m_tholabah not in (select id_m_tholabah from b_sub_sy where id_a_bulan = '.$id_a_bulan.')');

		$query = $this->db->get();		
		return $query->result();
	}

	function save($dataInsert)
	{
		$query = $this->db->insert('b_sub_sy', $dataInsert);
		return $query;
	}
	
	function delete($id_b_sub_sy)
	{
		$this->db->where('id_b_sub_sy', $id_b_sub_sy);
		$delete = $this->db->delete('b_sub_sy');
		return $delete;
	}

}